clear
set type double
set more 1

*plots curency choice by firm size for importing Appendix A2 figure
*use 2018 employment data
****************************************

*emp2018
use ../data/emp_wages_annual_2012to2019, clear
	keep if year==2018
	keep vat emp 
	rename emp emp2018
save temp/emp2018, replace


**********************************************
*8-bin plot by import value
*****************************************
use ../../rawdata/update_feb21/currency_extraEU_2017_2020, clear
	rename country cty_code
	merge m:1 vat cncode cty_code year month using ../data/mcurrencychoice_estdata, keepusing(Im) nogen
	drop if missing(Im)

drop date *X
	
	drop if missing(valueI)
    	drop if missing(currency)
	drop if cty_code=="EU"
	rename valueI value

	merge m:1 vat using ../data/manuf_firm, keep(match) nogen
	merge m:1 cty_code using ../data/usd_peg, keep(match master) nogen
	merge m:1 cty_code using ../rawdata/iso_cty_and_currency, keep(match master) nogen
	replace cty_name="User" if cty_code=="QW"
	
*bin plot
	drop if missing(currency)
	keep if year==2018
	
save temp/temp, replace
	
	gen type="EUR" if currency=="EUR"
	replace type="US" if currency=="USD"
	replace type="LC" if currency==currency_code & missing(type)
	replace type="NA" if missing(type)
	
	replace value=value/10^6
	gen value_EUR=value*(type=="EUR")
	gen value_US=value*(type=="US")
	gen value_LC=value*(type=="LC")
	gen value_NA=value*(type=="NA")
	
	
	collapse (sum) value* , by(vat)
	merge m:1 vat using temp/emp2018, keep(match)
	
	sort emp 
		
	gen bin=1 if emp<=50
	replace bin =2 if emp<=100 & missing(bin)
	replace bin =3 if emp<=200 & missing(bin)
	replace bin =4 if emp<=350 & missing(bin)
	replace bin =5 if emp<=550 & missing(bin)
	replace bin =6 if emp<=850 & missing(bin)
	replace bin =7 if emp<=2000 & missing(bin)
	replace bin = 8 if missing(bin) 
	
	egen maxl=max(emp), by(bin)
	gen nfirms=1

	collapse (sum) value* nfirms, by(bin maxl)
	gen sh_EUR=value_EUR/value
	gen sh_US=value_US/value
	gen sh_LC=value_LC/value
	gen sh_NA=value_NA/value
	
	drop value*
	order bin nfirms maxl sh*
	
export excel using ../results/sumstats/FigureA2, sheet(mcurrency_size_all) firstrow(variables) replace keepcellfmt	


*2. add a fourth category for pegs for noneuro
use temp/temp, clear
	gen type="EUR" if currency=="EUR"
	replace type="US" if currency=="USD"
	replace type="LC" if currency==currency_code & missing(type)
	replace type="NA" if missing(type)

	replace value=value/10^6
	gen value_EUR=value*(type=="EUR")
	gen value_US_nopeg=value if type=="US" & usd_peg==0
	gen value_LC_nopeg=value if type=="LC" & usd_peg==0
	
	gen value_US_peg=value if type=="US" & usd_peg==1
	gen value_LC_peg=value if type=="LC" & usd_peg==1
	gen value_NA=value*(type=="NA")
	
	collapse (sum) value* , by(vat)
	merge m:1 vat using temp/emp2018, keep(match)

	sort emp 

	gen bin=1 if emp<=50
	replace bin =2 if emp<=100 & missing(bin)
	replace bin =3 if emp<=200 & missing(bin)
	replace bin =4 if emp<=350 & missing(bin)
	replace bin =5 if emp<=550 & missing(bin)
	replace bin =6 if emp<=850 & missing(bin)
	replace bin =7 if emp<=2000 & missing(bin)
	replace bin = 8 if missing(bin) 

	egen maxl=max(emp), by(bin)
	gen nfirms=1

	collapse (sum) value* nfirms, by(bin maxl)
	gen sh_EUR=value_EUR/value
	gen sh_US_nopeg=value_US_nopeg/value
	gen sh_LC_nopeg=value_LC_nopeg/value
	gen sh_LC_peg=value_LC_peg/value
	gen sh_US_peg=value_US_peg/value
	gen sh_NA=value_NA/value
		
	drop value*
	order bin maxl nfirms sh*
	
export excel using ../results/sumstats/FigureA2, sheet(mcurrency_size_4cat, replace) firstrow(variables) keepcellfmt		


*3. drop US and pegged
use temp/temp, clear
	drop if usd_peg==1
	
	gen type="EUR" if currency=="EUR"
	replace type="US" if currency=="USD"
	replace type="LC" if currency==currency_code & missing(type)
	replace type="NA" if missing(type)

	replace value=value/1000000
	gen value_EUR=value*(type=="EUR")
	gen value_US=value*(type=="US")
	gen value_LC=value*(type=="LC")
	gen value_NA=value*(type=="NA")
	
	collapse (sum) value* , by(vat)
	merge m:1 vat using temp/emp2018, keep(match)

	sort emp 

	gen bin=1 if emp<=50
	replace bin =2 if emp<=100 & missing(bin)
	replace bin =3 if emp<=200 & missing(bin)
	replace bin =4 if emp<=350 & missing(bin)
	replace bin =5 if emp<=550 & missing(bin)
	replace bin =6 if emp<=850 & missing(bin)
	replace bin =7 if emp<=2000 & missing(bin)
	replace bin = 8 if missing(bin) 

	egen maxl=max(emp), by(bin)
	gen nfirms=1

	collapse (sum) value* nfirms, by(bin maxl)
	gen sh_EUR=value_EUR/value
	gen sh_US=value_US/value
	gen sh_LC=value_LC/value
	gen sh_NA=value_NA/value

	drop value*
	order bin nfirms maxl sh*
	
export excel using ../results/sumstats/FigureA2, sheet(mcurrency_size_nopeg, replace) firstrow(variables) keepcellfmt		



